﻿CREATE PROCEDURE [dbo].[proc_Customer_Extract]
	(
		@UserName varchar(20),
		@IndustryOneId varchar(20),
		@IndustryTwoId varchar(20),
		@rndID varchar(200)
	)
AS	
Begin
	Declare @companyid int,@num int,@Days int
	Declare	@Sql varchar(3000)
	Declare @Extract int

	Set @Days = -10	--10日内批注过的客户不再参与申领
	Select @Extract=DateDiff(dd,'1900-1-1',getdate())	--申领日期(今天)
	EXEC @companyid=proc_Company_GetID @UserName,@companyid--单位ID
	Select @num=Count(Id) From Customer Where ProtectName=@UserName And ProtectId=1 And Companyid=@companyid And ExtractDate<=@Extract --用户现有的公库客户数(不含自己当日新录入的客户)
	If @num=0
	Begin
		If @IndustryOneId='-1'	--所有行业
		Begin
			Set @Sql=';WITH list As(Select ROW_NUMBER() OVER (Order By newid()) AS Row,Id
				From Customer Where CompanyId='+Convert(varchar(10),@companyid)+' And Audit=1 and UpTime<DateAdd(day,'+Convert(varchar(10),@Days)+',GetDate()) And
				(ProtectName Is Null) And ProtectId=1 And delFlag=0 And OrdersCount=0 And (CustomerName Not like ''%免打扰%'')'

			if @IndustryTwoId='0'  --二级行业为0时
				Set @Sql = @Sql + '  And  IndustryTwoId=' + @IndustryTwoId + ')'
			else 
				Set @Sql = @Sql + ' )'
				
			Set @Sql = @Sql + ' Update a Set a.ProtectName='''+@UserName+''',ExtractDate='+Convert(varchar(10),@Extract)+' From Customer a,(Select Id From list Where Row In('+@rndID+')) b Where a.id=b.id'
			Exec(@Sql)
			RETURN @@rowcount
		End
		Else	--指定的一级行业
		Begin
			Set @Sql=';WITH list As(Select ROW_NUMBER() OVER (Order By newid()) AS Row,Id
					From Customer Where CompanyId='+Convert(varchar(10),@companyid)+' And Audit=1 And UpTime<DateAdd(day,'+Convert(varchar(10),@Days)+',GetDate()) And
					(ProtectName Is Null) And ProtectId=1 And delFlag=0 And OrdersCount=0 And (CustomerName Not like ''%免打扰%'') And '

			If @IndustryTwoId='-1'	--所有指定的一级行业
				Set @Sql = @Sql + 'IndustryOneId=' + @IndustryOneId + ')'
			Else	--指定的二级行业 --二级行业参数可能为0，查询条件需要加上一级行业限定[顾世杰2013.10.23] 
				Set @Sql = @Sql + ' IndustryOneId=' + @IndustryOneId + ' and IndustryTwoId=' + @IndustryTwoId + ')'

			Set @Sql = @Sql + ' Update a Set a.ProtectName='''+@UserName+''',ExtractDate='+Convert(varchar(10),@Extract)+' From Customer a,(Select Id From list Where Row In('+@rndID+')) b Where a.id=b.id'
			Exec(@Sql)
			RETURN @@rowcount
		End
	End
End
